﻿-- -----------------------------------------------------------------
-- 
-- CREATE TRIGGER trginsertpo_line_change
--   AFTER INSERT
--   ON purchase_order_line
--   FOR EACH ROW
--   EXECUTE PROCEDURE trgfninsertpo_line_change();
-- 
--  CREATE OR REPLACE FUNCTION trgfninsertpo_line_change()
--   RETURNS trigger AS
--   $BODY$
-- 	Begin
-- 		Declare 
-- 			using_id integer:=0;
-- 		if TG_OP='DELETE' then
-- 			using_id = old.order_id
-- 		else
-- 			using_id = new.order_id
-- 		end;
-- 		update purchase_order set line_update=True where id=new.order_id;
-- 		return NULL;
-- 	End;
--   $BODY$
--   LANGUAGE plpgsql VOLATILE
--   COST 100;



-- Function: trgfnwhen_insert_update_delete_pol_updatepo()

-- DROP FUNCTION trgfnwhen_insert_update_delete_pol_updatepo();

CREATE OR REPLACE FUNCTION trgfnwhen_insert_update_delete_pol_updatepo()
  RETURNS trigger AS
$BODY$
		DECLARE
			using_id integer:=0;
			using_order_line_id integer:=0;
		Begin
			IF TG_OP='INSERT' THEN
				Begin
					using_id = new.order_id;
					using_order_line_id = new.id;
				End;
			ELSEIF TG_OP='UPDATE' THEN
				Begin
					IF COALESCE(OLD.plan_qty,0)<>COALESCE(NEW.plan_qty,0) OR COALESCE(OLD.PRODUCT_QTY,0)<>COALESCE(NEW.PRODUCT_QTY,0) OR COALESCE(OLD.PRICE_UNIT,0)<>COALESCE(NEW.PRICE_UNIT,0) THEN
						Begin
							using_id = OLD.order_id;
							using_order_line_id = new.id;
						end;
					END IF;

				end;
			ELSE
				begin
					using_id = OLD.order_id;
					using_order_line_id = old.id;
				end;
			end if;
			IF USING_ID>0 THEN
				Begin
					Update purchase_order 
					set 
						amount_untaxed=(Select  
								sum(price_unit*case when state<>'done' and po.special_case=True then plan_qty else product_qty end) as price_subtotal
								from
								    purchase_order po
								left join
								    purchase_order_line pol on po.id = pol.order_id
								where po.id=using_id group by po.id),
						amount_total2=(Select  
								sum(price_unit*case when state<>'done' and po.special_case=True then plan_qty else product_qty end) as price_subtotal
								from
								    purchase_order po
								left join
								    purchase_order_line pol on po.id = pol.order_id
								where po.id=using_id group by po.id)+discount, 
						tax_amount=round(((Select  
								sum(price_unit*case when state<>'done' and po.special_case=True then plan_qty else product_qty end) as price_subtotal
								from
								    purchase_order po
								left join
								    purchase_order_line pol on po.id = pol.order_id
								where po.id=using_id group by po.id)*coalesce(tax_per,0)/100.0)::numeric,0)
					where id=using_id;
					Update purchase_order_line
					set
						price_subtotal = 
								price_unit * 
								case when 
									(Select 
										state 
									from 
										purchase_order po 
									where id = 
									      (Select 
											order_id 
										from purchase_order_line pol where id = using_order_line_id))<>'done' 
								then plan_qty
								else product_qty end
					where id = using_order_line_id;
				End;				
			END IF;
			Return NULL;
		end;
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


-- CREATE TRIGGER trgdupdate_insert_pol_updatepo_amount
--   AFTER INSERT OR UPDATE OR DELETE
--   ON purchase_order_line
--   FOR EACH ROW
--   EXECUTE PROCEDURE trgfnwhen_insert_update_delete_pol_updatepo();
